package com.dbms.utils;

import com.dbms.domain.entity.BisXajmodelArearate;
import com.dbms.domain.vo.DataObject;
import com.dbms.domain.vo.StcdInfo;
import com.dbms.service.BisReferencedDataService;
import com.dbms.service.BisXajmodelArearateService;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Component
@RequiredArgsConstructor
public class ExcelParser {

    final BisReferencedDataService bisReferencedDataService;
    final BisXajmodelArearateService bisXajmodelArearateService;

    public List<DataObject> parseExcel(File excelFile) throws IOException {
        List<DataObject> resultList = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(excelFile);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 获取表头行
            Row headerRow = sheet.getRow(0);
            int numColumns = headerRow.getLastCellNum();
            String[] headers = new String[numColumns];
            for (int i = 0; i < numColumns; i++) {
                Cell cell = headerRow.getCell(i);
                if (cell!= null) {
                    headers[i] = cell.getStringCellValue();
                }
            }

            // 遍历数据行
            int lastRowNum = sheet.getLastRowNum();
            for (int rowIndex = 1; rowIndex <= lastRowNum; rowIndex++) {
                Row dataRow = sheet.getRow(rowIndex);
                if (dataRow!= null) {
                    DataObject dataObject = new DataObject();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm");
                    Date time;
                    try {
                        time = dataRow.getCell(0).getDateCellValue();
                        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        dataObject.setTime(outputFormat.format(time));
                    } catch (Exception e) {
                        continue;
                    }
                    // 实测流量
                    dataObject.setMq("0");
                    // 蒸发
                    dataObject.setErate(String.format("%.2f",  bisReferencedDataService.getRandomData().getZfxl()));

                    List<StcdInfo> stcdInfoList = new ArrayList<>();
                    for (int columnIndex = 1; columnIndex < numColumns; columnIndex++) {
                        StcdInfo stcdInfo = new StcdInfo();
                        stcdInfo.setStcd(headers[columnIndex]);
                        Cell valueCell = dataRow.getCell(columnIndex);
                        if (valueCell!= null) {
                            stcdInfo.setDrp(String.valueOf(valueCell.getNumericCellValue()));
                        } else {
                            stcdInfo.setDrp("0");
                        }
                        stcdInfoList.add(stcdInfo);
                    }
                    BisXajmodelArearate bisXajmodelArearate = bisXajmodelArearateService.queryByStcdP(stcdInfoList.get(0).getStcd());
                    if(ObjectUtils.isNotEmpty(bisXajmodelArearate)) {
                        dataObject.setStcd(bisXajmodelArearate.getStcdR());
                    }else{
                        dataObject.setStcd(null);
                    }
                    dataObject.setStcdinfo(stcdInfoList);
                    resultList.add(dataObject);
                }
            }
        }

        return resultList;
    }
}
